Cream of the Crop 1

home *** CD-ROM | disk | FTP | other *** search

/ Cream of the Crop 1 / Cream of the Crop 1.iso / BUSINESS / REBEL.ARJ / REBEL.DOC < prev next >

Wrap

Text File | 1992-02-25 | 147KB | 2,977 lines

_ __ __ __ __ _ ' ) ) / ` / ) / ` / ` tm /--' /-- /--< /-- / / \ (____, (____) (____, (____, Version 1.0la U S E R M A N U A L (c) Copyright 1992 Brad L. Smith All Rights Reserved DISCLAIMER THIS SOFTWARE AND MANUAL IS LICENSED "AS IS" AND WITHOUT ANY EXPRESSED OR IMPLIED WARRANTIES WHATSOEVER. THE USER MUST ASSUME THE ENTIRE RISK: 1) OF USING THIS PRODUCT; 2) OF ANY DAMAGES RESULTING FROM ITS USES; 3) FOR ITS FITNESS FOR ANY PARTICULAR PURPOSE. UNDER NO CIRCUMSTANCE, WILL LIABILITY EXCEED THE ORIGINAL REGISTRATION FEE. LICENSE AGREEMENT This is copyrighted software, distributed as shareware, at the lowest possible price. Unregistered users are granted a limited license to EVALUATE this product on a TRIAL BASIS ONLY. Unmodified copies of the executable (.EXE) and documentation (.DOC) files may be distributed in both the United States and Canada - provided that no payment, other than for the media, is received. This software may not be reverse engineered, altered, or included as a part of any product or service without the expressed written consent of the copyright holder. Registered users are granted a nontransferable license to use this version of REBEL for life. ----------------------------------------------------------------------- Name: _____________________________________________________ (Company: _________________________________________________) Address: __________________________________________________ City: _______________________ State: ___ Zip: __________ Telephone: (_____) ____________________ Disk (check one) 3.5" ___ or 5.25" ___ License Number Each Total Individual (1 user) _________ $25.00 ____________ Site (up to 5 users) _________ $50.00 ____________ Site (up to 20 users) _________ $100.00 ____________ Shipping and Handling: $5.00 Total: ___________ REBEL SOFTWARE - P.O. Box 566 - Fort Collins, CO - 80522-0566 ----------------------------------------------------------------------- As a registered users, you will receive discounts on future releases and a special telephone support number. Your comments are always appreciated! ______________________ ______________________________________________/ TABLE OF CONTENTS Chapter 1 INTRODUCTION Chapter 2 GETTING STARTED Chapter 3 TUTORIAL Chapter 4 FUNCTION KEY MENUS 1 CELL (change cell display attributes) 2 EDIT (edit/delete/copy/shift cells) 3 FILE (load/save worksheets) 6 WINDOWS (set column width; create windows) 7 UTILITIES (view memory; sum/recalc cells) 8 DEFAULT (set global default values) 9 FORMATS (set numeric/date/time displays) Chapter 5 EDIT MODE Chapter 6 TECHNICAL INFORMATION 1 Cell Types 2 Cell Addresses 3 Operators 4 Formulas 5 Arrays 6 Multiple Worksheets 7 Range Operations 8 Direct Cell Addresses 9 Indirect Cell Addresses 10 When are Formulas Recalculated? 11 Forcing Formulas to Recalculate 12 Optimizing your worksheet 13 Miscellaneous Topics Chapter 7 SHORT CUT COMMANDS Chapter 8 FUNCTIONS 1 Math 2 Trig 3 Financial 4 Date 5 Time 6 String 7 Misc _________________________ ___________________________________________/ Chapter 1 INTRODUCTION The way in which spreadsheets manipulate data has changed little over the years. This is somewhat surprising given the advances that have been made in almost every other area of the computer field. Just pick up the oldest spreadsheet manual you can find and notice how similar the formulas are to the ones you can write today. REBEL has attempted to address this problem by abandoning the de facto standard that has been adopted over the past decade and replacing it with a simpler, more powerful syntax. A major effort has gone into making this one of the easiest (if not THE easiest) spreadsheet there is to learn; as well as, one of the most powerful 'pure' spreadsheets you'll ever use. You will find that the new syntax used by REBEL resembles a true programming language that builds on a few simple rules to perform more and more complex operations. Here are just a few examples: CELL ADDRESSES While most spreadsheets on the market today use letters to refer to the columns of other cells, REBEL uses numbers (e.g. 'AB12' would be equivalent to [12,28] in REBEL - row 12 column 28). INDIRECT CELL ADDRESSING The ROW and/or COLUMN fields of a Cell Address can themselves be expressions, allowing Cell Addresses to be nested. This means that a cell reference can vary, depending on the values of other cells (very handy for table lookups). [ROW, [row,column] ] \__________\ \__ The value from this cell will be used as COLUMN number ARRAYS Upto 8000 values (array elements) can be assigned to each cell! Each of these values can be accessed by including the optional 3rd Array Element field in the Cell Address: [row,column,ELEMENT] MULTIPLE WORKSHEETS Upto 4 separate worksheets can be loaded at one time. Cells from any of these worksheets can be individually addressed by including the optional 4th Worksheet Level field in Cell Address: [row,column,element,WORKSHEET] NOTE: Both the 'Array Element' and 'Worksheet' fields of a Cell Address are optional. Their values default to zero if not included. PROGRAMMABLE RANGE OPERATIONS A 'Range Operation' can be defined that assigns a series of 'formulas' to a GROUP of cells which returns a single value upon completion. The general form of this 'Range Operation' is similar to that of a standard Cell Address and can be used within other formulas in much the same way. [ Range; formula1; formula2; etc. ] OVER THIRTY (30+) OPERATORS: Unary ! + - Arithmetic ** * / % + - Relational > >= <= < == != Logical && || Bitwise << >> & | Ternary ?:; Assignment = += -= *= /= %= <<= >>= &= |= OVER FIFTY (50+) Math, Trig, Financial, String, Date and Time functions. VARIABLE LABELS that are rewritten each time the worksheet is recalculated REBEL does not stop here, however. Every effort has been made to make this the simplest spreadsheet you have ever used. Function keys are designed to act like built in macros that perform complex editing tasks, which you would otherwise be forced to develop yourself. SHIFT cells around your worksheet by pressing a single key. UNDELETE cells mistakenly lost SEARCH and REPLACE (with wildcards) CUT and PASTE editing and more . . . SPLIT SCREENS (unlimited) MINIMAL RECALC ERROR MESSAGES SINGLE STEP FORMULA DEBUGGER ! _____________________________ ________________________________________/ Chapter 2 GETTING STARTED When REBEL is first invoked, you will be presented with an empty worksheet at the 'Root Menu Level'. Each cell can have either a 'label' (text string) or a 'formula' associated with it. Formulas consist of numbers or expressions that may or may not be based on the results of other cells. The screen is divided into three regions: 1) the "Function Key Menu" at the top; 2) the "Input Line" for entering text or formulas; and 3) the "Cells" that comprise the worksheet. The options, displayed by the Function Key Menus, if written entirely in upper case letters, will re-assign a new set of options to each function key; or, if they are abbreviated in both upper and lower case letters, they will perform a specific action. The first menu you will encounter upon entering the spreadsheet is referred to as the "Root Menu". _______ _______ _______ _______ | f1 | | f2 | | f3 | | f0 | | CELL | | EDIT | | FILE | . . . | EXIT | (Worksheet |__________________________________________________ Level) ---> <4>______1__________2_________3__________4_________ |1| |2| "The Root Menu _______ _______ _______ _______ Function Key | f1 | | f2 | | f3 | | f0 | <-- Menu" | CELL | | EDIT | | FILE | . . . | EXIT | |_________________________________________________ <-- "Input Line" <1>______1__________2_________3__________4________ <-- (Column Bar) |1| |2| <-- (Row Bar) 15.00 |3| __________ |4| | 25.00| <-- "Cell Pointer" |5| (active cell) |6| : [2,3]+10 <-- (formula of active cell) Two cursors are displayed on the screen. The large highlighted area on the worksheet is referred to as the "Cell Pointer". It points to the currently active cell that is about to accept a new or modified entry. This cursor (or pointer) can be moved at any time with arrow keys on your keyboard. The second, smaller cursor is located on the "Input Line" (just below the Function Key Menu). It is used to enter a text or formulas into the cell highlighted by the Cell Pointer. The remaining portion of this section will provide you with a quick overview of some of the basics you will need to get started. You'll learn how to start REBEL, how to move between worksheet levels, how to use the Function Key Menus, and how to enter data. For more details on each of these topics, refer to the Technical Information Section (Chapter 6). ______________________________________________________________________ | | Starting | If you will be starting this program from a floppy from floppy | disk (assumed to be in drive A), type the following drive | sequence of commands from your computer's system | prompt: | | A: <Enter> (moves you to Drive A) | CD \ <Enter> (change to root directory) | | REBEL <Enter> (for color mode) | or | REBEL -b <Enter> (for black and white mode) | | Installing | To permanently install REBEL on your hard drive (assumed REBEL on a | to be Drive C), you need only copy the .EXE and .DOC Hard Disk | files from the floppy disk. The following series of | commands is but one example of how to do this: | | C: <Enter> (moves you to Drive C) | CD \DOS <Enter> (change to a subdirectory | called: 'DOS' if it exists) | | COPY A:*.EXE *.* <Enter> | COPY A:*.DOC *.* <Enter> | | (If you create a separate subdirectory for the REBEL | .EXE and .DOC files, be sure to include it in your | search path; otherwise, you'll need to move to THAT | directory to execute the program.) | | Root Menu | A variety of operations can be performed using the | function keys on your keyboard. The specific task | assigned to each of these keys can vary, however, | depending on the menu displayed at the top of your | screen. The first menu that appears when you startup | the program is called the 'Root Menu'. | _______ _______ _______ _______ | | f1 | | f2 | | f3 | | f0 | | | CELL | | EDIT | | FILE | . . . | EXIT | | | This menu is used (as a starting point) to re-assign | new operations to the function keys. The words, | highlighted in each of the boxes, are designed to | briefly describe the type of commands that you can | expect to gain access to - if pressed. Notice that | the descriptive words of this menu are written in | UPPER CASE letters - as opposed to both upper and | lower case letters used to describe commands. | | Return to | In most menus, the F1 function key (DONE) will return Root Menu | you to the Root Menu. | | Exit | You can terminate your session by pressing the F10 | function key (EXIT) from the "Root Menu". | | Moving the | You can move the Cell Pointer from cell to cell with Cell | the Arrow Keys or jump from screen to screen with the Pointer | following keys: | | <Pg Up> moves Cell Pointer UP one screen. | <Pg Dn> moves Cell Pointer DOWN one screen. | <Home> moves Cell Pointer LEFT one screen. | <End> moves Cell Pointer RIGHT one screen. | | NOTE: You can not move the Cell Pointer with the arrow | keys once you have started an entry. | | Using the | You can move the Cell Pointer directly to any cell on "GOTO" | worksheet, without using the arrow keys. This can be Command | very handy if you need to move the Cell Pointer a long | distance or to another worksheet at a different level. | To do this, simply type a slash (/) at the Input Line | (located above the Column Bar). You will then be asked | to enter the Row, Column, and Worksheet Level numbers | to move the Cell Pointer to. | | NOTE: You can accept the Row, Column, of Level number | that is enclosed within the square brackets of each | prompt by pressing ONLY the <Enter> key. Otherwise, | type the number you want at the prompt and then press | <Enter>. | | Escaping | If you do not wish to complete an option that is from a | prompting you for information, press the <Esc> key Prompt | to abort. You will be returned to the Input Line. | | Entering | All cell entries are made from the Input Line (above Formulas | the Column Bar). A special 'Edit Mode' is also and Labels | available to modify existing entries (refer to | Chapter 5). | | IMPORTANT! Never being a decimal number with | a zero (unless, of course, the number is zero). | That is, if you want to enter "45", don't type | it as "045" (if you do, you'll get the decimal | equivalent of OCTAL 45). Refer to Section 6.13 | "Misc Topics" for an expanded discussion. | Referencing | The value of any cell within the spreadsheet can be values in | used in another cell's formula by surrounding the other cells | ROW and COLUMN number (of the cell that contains | the value) with square brackets []. This is called | a "Cell Address". | | [row,column] | | Example: If you want to multiply the value in | cell [1,2] (row 1, column 2) by 5 and display | the answer at your current cell location, the | formula might look something like this: | | [1,2] * 5 | | Cell Types | A cell takes on one of three possible 'CELL TYPES' | whenever you enter data (FORMULA, LABEL, or VARIABLE | LABEL). The spreadsheet will automatically choose | the cell type for you; and, in most cases, it will do | it correctly. There are, however, a few cases that | can confuse the spreadsheet; so, it is worth a brief | look to see how the spreadsheet interprets each: | | FORMULAS This cell type is assigned to a cell | whenever a number, expression, or | function is entered. For example: | | 1.00 (number) | [1,2]+200 (expression) | +sqrt(16) (function) | | Any entry that begins with one of the | following characters: | | + - ( [ . 0 1 2 3 4 5 6 7 8 9 | | will be automatically be interpreted as a | mathematical 'formula' and the spreadsheet | will attempt to compute its value. It is | important, however, to remember that some | formulas may NOT begin with one of these | characters. An example of this might be | an expression that begins with a function | call. In these cases, you should start | the expression with a Plus Sign (+) to | FORCE it to be interpreted as FORMULA. | | | LABELS A LABEL is defined as a string of | characters that is displayed exactly as | entered, which DOES NOT BEGIN with one of | the characters that would otherwise cause | it to be interpreted as an FORMULA. The | spreadsheet attaches no special meaning to | these entries. | | Example: January the 1st | | Note: If a LABEL must begin with one of | the characters that defines an FORMULA, a | Single Quote (') can be used force the | spreadsheet to interpret it as a LABEL. | The optional single quote ('), at the | beginning of the entry, will not appear | when the LABEL is displayed. | | Example: '1st of January | | | VARIABLE This is a special type of LABEL that MUST | LABEL begin with a Double Quote ("). Entries | of this type are displayed exactly as | they are typed, except when a Cell Address | (e.g. "[1,2]") is encountered within the | string. In this case, the address is | replaced by the 'contents' of the cell. | | Example: If the cell at [1,1] (row 1, | column 1) contains the label: 'red | and you then make the following entry | at cell [3,1]: | | "My car is [1,1]. | | it will actually display: | | My car is red. | | Note: The Double Quote (") IS REQUIRED to | force [1,1] to be replaced by the contents | of that cell. When you're done, try typing | something else in cell [1,1] and see what | happens! _________________________ ___________________________________________/ Chapter 3 TUTORIAL In the following tutorial, you will design a simple spreadsheet that can be used to compute your monthly car or mortgage loan payment. It should end up look something like this: Loan size: 10000.00 Annual Interest Rate: 0.12 Length of Loan (years): 3.00 Monthly Payment: 332.14 The worksheet must be empty before you can begin this exercise. If you have made any kind of entry, you can clear it by simply EXITING the program and then re-starting it. Later on you will learn easier ways of doing this. If you make a mistake or you wish to change a value, simply move the Cell Pointer back to the cell and re-type the entry. MAKE SURE YOU ARE ON THE RIGHT CELL BEFORE YOU START TYPING! ______________________________________________________________________ | Step 1 | Using the arrow keys, move the Cell Pointer to cell | [3,2] (row 3, column 2) | Step 2 | Type "Loan size:" (without the quotes) | Step 3 | Move to cell [3,5] | Step 4 | Type: "10000" (without the quotes) | Step 5 | Move to cell [4,2] | Step 6 | Type: "Annual Interest Rate:" (without the quotes) | Step 7 | Move to cell [4,5] | Step 8 | Type: ".12" (without the quotes) | Step 9 | Move to cell [5,2] | Step 10 | Type: "Length of Loan (years):" (without the quotes) | Step 11 | Move cell [5,5] | Step 12 | Type: "3" (without the quotes) | Step 13 | Move to cell [7,3] | Step 14 | Type: "Monthly Payment:" (without the quotes) | Step 15 | Move to cell [7,5] | Step 16 | Type: "+pmt([3,5],[4,5]/12,[5,5]*12)" (no quotes) | If you got "332.14" for the payment, congratulations, you entered everything right! Now, you can compute any payment you like, simply by re-entering the appropriate values in cells [3,5] thru [5,5]. The next steps describe how to save this worksheet. | Step 17 | From the Root Menu, press function key: | | F3 (FILE) | Step 18 | You should now see a new set of options displayed | on the "Function Key Menu". These options are used | to SAVE and LOAD (retrieve) worksheets. From this | set of options, press function key: | | F4 (Save) | Step 19 | You should see a prompt, requesting the name of the | file to store this worksheet in. At this prompt | enter: | | "payments" <Enter> (without the quotes) | ________________________________ _____________________________________/ Chapter 4 FUNCTION KEY MENUS The Function Key Menu, at the top of your screen, is composed of two types of options. Those that: 1) perform specific operations or commands 2) and those that assign new options to the function keys. The word in the descriptive box that identifies each option will help you determine how it is used. That is, a function key that loads a new set of menu options will always be written in UPPER CASE LETTERS; while function keys that perform commands, will always be written in BOTH UPPER AND LOWER CASE LETTERS. Occasionally, when you press a function key, a prompt will appear requesting additional information. These prompts have a specific format that's worth learning: Question... (options)? [default]: _ | | the range of legal _______| |____ the default answer answers are displayed that will be used within parenthesises if only the <Enter> key is pressed Important! If, for any reason, you should need to abort an option without completing it, simply press the <Esc> key. You will be returned to the Input Line and no further action will be taken. Note: Some prompts will request that you move the Cell Pointer to another location with the arrow keys (i.e. when you need to define the lower right hand corner of a range of cells). Normally, the arrow keys work fine for this - except when you need to move long distances. In these situations, the Forward Slash (/) "Goto" Command (see Chapter 7) can be used at the prompt to jump directly to any cell on the worksheet. The remainder of this section describes each of the Function Key Menus in detail. Most options non-destructive, with the exception of the Clear, Purge, and Delete options; so, don't be afraid to try them. Remember, the <Esc> key will break you out of almost anything. The Root Menu Index _______ | f1 | Section 4.1 | CELL | Displays a new set of menu options that allow you to modify the ATTRIBUTES of an existing cell (Precision, Arrays, Hide, Protect, Recalc, Color). _______ | f2 | Section 4.2 | EDIT | Displays a new set of menu options that allow you to ALTER THE LAYOUT of cells on the worksheet or EDIT the contents of an existing cell (Edit, Undo, Join, Justify, Purge, Move, Copy, Shift, Insert, Delete). _______ | f3 | Section 4.3 | FILE | Displays a new set of menu options that allow you to READ and/or WRITE to files in a variety of different formats (Load, Save, Report, Text, Dump, DIF). _______ | f6 | Section 4.6 |WINDOWS| Displays a new set of menu options that allow you to create new windows on the screen (Make/Remove Windows, Width, Level, Margins). _______ | f7 | Section 4.7 |UTILITY| Displays a new set of menu options that perform general UTILITY functions (Sum, Recalculation, Memory). _______ | f8 | Section 4.8 |DEFAULT| Displays a new set of menu options that allow you to alter the GLOBAL DEFAULT settings that are used whenever a new cell is first created (Column_Width, Precision, Color, and Range Recalc DEFAULTS!). Most of these default settings can be overridden on a case-by case basis. _______ | f9 | Section 4.9 |FORMATS| Displays a new set of menu options that allow you to modify the way in which numeric values are displayed (Dec, Sci, Oct, Hex, Bin, Comma, $, %, Time) _______ | f0 | Section 4.10 | EXIT | _______________________________ Section 4.1 CELL ATTRIBUTES ......... : f1 : From the "Root Menu", press F1 to access the : CELL : following set of Function Key options. _______ _______ _______ | f1 | | f2 | | f3 | | DONE | | x.xx | | Array | . . . f1 - Returns you to the Root Menu. f2 - Changes a cell's fixed DECIMAL format, controlling the number of decimal places that will appear when the cell's value is displayed. Changing this setting will not affect the actual value stored by the spreadsheet. Numbers can be displayed with up to 14 decimal places. If the existing column width is not large enough to handle the format, a series of asterisks (****) will appear instead of the expected value. Whatever the case, the value of any cell can always be displayed at the Input Line, using the "=0" Display Element Command (see Chapter 7). f3 - Creates an ARRAY SPACE of up to 8000 values that can be assigned to a FORMULA cell, highlighted by the Cell Pointer (refer to Section 6.5 - Arrays). The values stored in this array area can be addressed within any mathematical 'formula' by including the Array Element field in the Cell Address - [row,col,ELEMENT]. These values can also be viewed or loaded at any time using the (=e) Display Element Commands described in Chapter 7. Other, more advanced loading, techniques are discussed in Section 4.3 - Loading Text Files and in Section 6.5 - Arrays. ___________________________ | f4 f5 f6 | | Display Cell OKAY? | | Hide Row | | Protect Column | | Unprot All | | Blink Range | | noBlink | | +ReCalc | | -ReCalc | | +NoCalc | . . . | -NoCalc | . . . Function keys F4 and F5 work together to form a user defined option that is executed when the F6 (OKAY?) key is pressed. The F4 and F5 keys do not perform any action other than to set up the option. f4 - Display: Causes a hidden cell to be redisplayed. Hide: Sets a flag, preventing a cell's contents form being displayed on the screen. Protect: Sets a flag, preventing a cell from being purged or edited. If you inadvertently attempt to modify the contents of a protected cell, you will be automatically placed in the 'Edit Mode' (see Chapter 5). You can then use the "CUT" option to temporarily save your entry until you are able to remove the protection flag. Otherwise. simply press the <ESC> key. UnProt: Removes the protective flag so that a cell can be purged. Blink: Causes a cell to blink on and off when displayed. noBlink: Prevents a cell from blinking when displayed. +ReCalc: Sets a flags that FORCES a cell to be RECALCULATED whenever ANY change is made to the speadsheet - even if the change does not directly affect the cell. -ReCalc: CLEARS the '+ReCalc' flag that forces a cell to be recalculated each time the spreadsheet is modified. +NoCalc: Sets a flag that PREVENTS a cell from being recalculated - even if that cell is directly affected by a change somewhere else in the spreadsheet. -NoCalc: CLEARS the '+NoCalc' flag. f5 - Defines the range of cells that will take on the attributes setup by the F4 key. f6 - Executes the user defined option setup by the F4 and F5 function keys. _____________________________________ | f7 f8 f9 f0 | | Cell White Low OKAY? | | Row Brw/Ylw High | | Col Magenta | | ALL Red | | Range Cyan | | Green | . . . | Blue | Function keys F7 thru F9 work together to form a 'user defined option' that is executed when the F10 (OKAY?) key is pressed. The F7, F8, and F9 keys do not perform any action other than to set up the option. NOTE: Cells that have their color display changed using this option will no longer be affected by changes made to the default color setting (see Section 4.8 - F7 thru F10). f7 - Defines the range of cells that will take on a new COLOR attribute. f8 - Defines a new display COLOR. f9 - Sets the display INTENSITY to High or Low. f0 - Executes the user defined option setup by the F7, F8, and F9 function keys. __________________ Section 4.2 EDIT ......... : f2 : From the "Root Menu", press F2 : EDIT : _______ _______ _______ _______ _______ _______ | f1 | | f2 | | f3 | | f4 | | f5 | | f6 | | DONE | | Edit | | Undo | | Join | |<Jstify| |Jstify>| . . . f1 - Returns you to the Root Menu. f2 - Activates the 'Edit Mode' Function Keys (see Chapter 5). f3 - UNDOES the last change made to a cell. f4 - JOINS the 'labels' of two adjacent cells. The Cell Pointer must be positioned over the left most label. f5 - LEFT JUSTIFIES the value displayed by a FORMULA cell. f6 - RIGHT JUSTIFIES the value displayed by a FORMULA cell. _____________________________________ | f7 f8 f9 f0 | | Purge Cell OKAY? | | Shift Row Left | | Move Column Right | | Copy All Up | | Insert Range Down | | Delete (rel) | | (abs) | . . . | (val) | Function keys F7 thru F9 work together to form a 'user defined option' that is executed when the F10 (OKAY?) key is pressed. The F7, F8, and F9 keys do not perform any action other than to set up the option. f7 Purge: Removes one or more cells, freeing the memory for other uses. Shift: Moves one or more cells one cell position up, down, left, or right. This command will only shift OCCUPIED cells that are ADJACENT to the Cell Pointer. As a result, the position of the Cell Pointer is important, as it can influence which cells are actually shifted. Take the following example. If you want to shift a 'column' of cells UP, LEFT, or to the RIGHT, you must place the Cell Pointer on the last occupied cell in the column that you wish to be included. This would be the cell with the 'highest' row number in its Cell Address. The cells that will be shifted will include everything above the Cell Pointer until the first empty cell is encountered. When shifting a 'column' of cells DOWN, place the Cell Pointer at the top of the column (the cell with the 'lowest' row number in its address). Everything below this point will be shifted until the first empty cell is encountered. Move: Moves one or more cells to a new location, anywhere on the worksheet. Copy: Copies one or more cells to a new location. The F9 function key is used to set the 'type' of cell copy that is to take place - relative (rel), absolute (abs), or value (val). - Relative (rel) copy means that when you copy a cell's formula from one location to another, the 'direct' Cell Addresses within the formula of the new cell will be adjusted (see Section 6.8 - Direct Cell Addresses). That is, the 'direct' Cell Addresses in the new cell will be modified to reference cells that are the same relative distance away from the new cell as they were from the original cell. - Absolute (abs) copy means that when you copy a cell's formula from one location to another, the Cell Addresses (within that formula) will not be adjusted. They will reference the same cells as the original cells do. - Value (val) copy means that only values (not the formulas associated with them) are copied. NOTE: Cells can be COPIED or MOVED from one worksheet level to another with the aide of the Forward Slash (/) 'Goto' Command (discussed in Chapter 7). To do it, simply type a slash (/) instead of using the arrow keys from the prompt, requesting you to move the Cell Pointer the the TARGET cell location. This will produce a new series of prompts that will allow you to jump to any Row, Column, or WORKSHEET LEVEL. Insert: Adds a new Row or Column (depending on the setting of function key F8) above the Cell Pointer's current position. Delete: Deletes the Row or Column (depending on the setting of function key F8) that currently contains the Cell Pointer. f8 Defines the range of cells that will be affected by the F7 setting. f9 The option associated with this key will only appear when either the 'Shift' or 'Copy' command have been set with the F7 function key. Otherwise, the function of this key is disabled and nothing will appear in its display. Left, Right, Up, Down: These options only become available when the F7 'Shift' command has been set. They establish the direction the cell(s) will be moved. (rel), (abs), (val): These choices only become available when the F7 'Copy' Command has been set. They establish the 'type' of cell copy that will take place - a Relative, Absolute, or Value cell copy. (Refer back to the discussion on the F7 'copy' option.) f0 Executes the user defined option setup by the F7, F8, and F9 function keys. ___________________ Section 4.3 FILES ......... : f3 : From the "Root Menu", press F3 : FILE : _______ _______ _______ _______ _______ | f1 | | f2 | | f3 | | f4 | | f5 | | DONE | |Report>| | Load< | | Save> | | Text< | . . . f1 - Returns you to the Root Menu. f2 - Copies a worksheet to a standard text file in your current working directory, exactly as it appears on your screen. The DOS 'PRINT' command can then be used to sent this file to the printer. The report will begin with the cell located in the upper left hand corner of the active window and will continue to the row you specify or until no further data is found, whichever comes first. You may also set the maximum number of characters per line (upto 132). If the default value for this setting is used, only the information visible on the screen will be sent to the file. f3 - LOADS (retrieves) worksheets from the current working directory that have been saved in REBEL's native ".RB1" format. Simply type in the name of the file that contains your worksheet and press <Enter>. It is not necessary to include the .RB1 extension - REBEL will do it for you. You can also review all of the worksheets that are currently available to you by pressing any of the <Arrow> keys (prior to making an entry). Each time you do this, one of the files that has a ".RB1" extension will be displayed within the prompt's square brackets []. When the file you are interested in appears, press the <Enter> key WITH NO OTHER INPUT to load it. The tilde (~) wildcard character can be used to create a pattern that will reduce the number of files you must to search through. If you do this, however, the pattern MUST be entered BEFORE the first arrow key is pressed. That is, once an arrow key is pressed - the pattern is set. If you press an arrow key before entering a pattern, the default pattern will be used (i.e. "~.RB1" which is all of the .RB1 files in the current working directory!). You may want to experiment with this option to see how it works. f4 - SAVES the currently active worksheet to a disk file, using REBEL's native ".RB1" format. It is not necessary to include the .RB1 extension when you enter the file name. f5 - Loads a TEXT FILE into the currently active worksheet, starting at the cell highlighted by the Cell Pointer. Each 'word' or 'number' in the file will be loaded into individual cells and 'typed' as LABELS. A series of words, however, can be loaded into a single cell by grouping them within double quotes ("..."). Existing data, within the worksheet, will not be overwritten. Text files can also be used to load the ARRAY SPACE of a cell. To do this, the cell must either be empty or contain an expression (i.e. be a FORMULA cell). A "HEADER LINE", that describes 'what' and 'where' to start loading the array, MUST precede the values to be loaded. The HEADER must begin with a tilde (~) and end with a semicolon (;). It's general format is described below: ~[row,column,MAX_ARRAY_SIZE,worksheet],START,END; For example, the following 5 line file entry will cause an Array Space (containing 15 elements) to be defined for the cell at [55,66,,1]. It will then load 4 values (25.0 thru 28.0), starting at array position 8 and ending at array position 12. ~[55,66,15,1],8,12; 25.0 26.0 27.0 28.0 An important thing to remember, is that the number in the ARRAY ELEMENT position of the HEADER LINE will define or re-define a cell's array Area Space - prior to loading it! Also note that the 'start' and 'end' values, which follows the HEADER's cell address, define the exact array positions to be loaded (8 thru 12). More than one such header entries may be included in a file. In addition to fixed numbers, expressions that access values in other cells throughout the worksheet, can also be used to load the array space. Take the following example. Here, 4 values (from another worksheet level) are added to a fixed number and then loaded into the array (elements 8 through 12). ~[55,66,12,1],8,12 25.0 + [1,1,,2] 26.0 + [1,2,,2] 27.0 + [1,3,,2] 28.0 + [1,4,,2] _______ _______ _______ _______ _______ | f6 | | f7 | | f8 | | f9 | | f0 | . . . | Dump> | | DIF <>| |ListDir| |ChngDir| | Clear | f6 - Outputs a 'formula' dump to a disk file for debugging purposes. f7 - LOADS and/or SAVES a columnwise DIF (Data Interchange Format) file, starting with the cell highlighted by the Cell Pointer. f8 - LISTS the files in the Current Working Directory. You can control the number of files that will be displayed by using the tilde (~) wildcard character to form a specific pattern. The default pattern (~.~) will display all files. Press the <Enter> key to accept the default; or enter your own pattern, following it with the <Enter> key. f9 - CHANGES the Current Working Directory. f0 - CLEARS the currently active worksheet. _____________________ Section 4.6 WINDOWS ......... From the "Root Menu", press : f6 : :WINDOWS: _______ _______ _______ _______ _______ | f1 | | f2 | | f3 | | f4 | | f5 | | DONE | | RmvWdw| |HorzWdw| | ChgWdw| |VertWdw| . . . _______ _______ _______ _______ _______ | f6 | | f7 | | f8 | | f9 | | f0 | . . . | Width | | Level | |<Margin| | SET | |Margin>| f1 - Returns you to the Root Menu. f2 - Removes the currently active window (i.e. the one that contains the Cell Pointer). The last window can not be removed. f3 - Creates a Horizontal Window, beginning at the Cell Pointer's position. f4 - Moves the Cell Pointer to the Next Window - if one exists. (also see the <Tab> Command in Chapter 7 for a faster way of doing this) f5 - Creates a Vertical Window, beginning at the Cell Pointer's position. f6 - Changes the WIDTH of the current column. You will also be asked to enter the number of the last column you wish this change to affect. Any change made by this option will override the default column setting (see Section 4.8 - dWidth). f7 - Allows you to change the Worksheet LEVEL of the current window. (The level of each window is displayed in the extreme left hand corner of the Column Bar - surrounded by angle brackets <>.). f8 - Moves the Cell Pointer to the next Margin - to the left of it's current position (see function key F9). f9 - Sets or Removes a MARGIN in the column occupied by the Cell Pointer. Once the Cell Pointer is move off this column, it can no longer be crossed using the arrow keys. f0 - Moves the Cell Pointer to the next Margin - to the right of it's current position (see function key F9). ________________________ Section 4.7 UTILITIES ......... From the "Root Menu", press : f7 : :UTILITY: _______ _______ _______ _______ _______ _______ _______ | f1 | | f2 | | f3 | | f4 | | f5 | | f6 | | f7 | | DONE | | Sum | | | |CelCalc| |FulCalc| | | | Memory|... f1 - Returns you to the Root Menu. f2 - Quickly SUMS a range of cells and displays the results at the Input Line. f4 - RECALCULATES the cell highlighted by the Cell Pointer. f5 - RECALCULATES every cell on the worksheet that contains the highlighted Cell Pointer. f7 - Displays the remaining amount of available MEMORY. _______________________________ Section 4.8 DEFAULT SETTINGS ......... From the "Root Menu", press : f8 : :DEFAULT: _______ _______ _______ _______ _______ | f1 | | f2 | | f3 | | f4 | | f5 | | DONE | | dRange| | | | dWidth| |dDecmal| . . . f1 - Returns you to the Root Menu. f2 - Sets the number of times a formula that contains a RANGE OPERATION will be recomputed for each change made to the worksheet. This setting is used to control the affects of circular references made within Range Operation (see Section 6.12 - Optimizing Your Worksheet). f4 - Changes the default COLUMN WIDTH setting for the current worksheet. This option will affect ALL of the columns that have not been individually set using the column width option under the WINDOWS menu (also see Section 4.6 - Width). f5 - Changes the default DECIMAL display format setting that is used whenever a NEW cell is created. This option is not intended to be used to modify the decimal display format of cells that differ from the default value (for that, see Section 4.1 - x.xx). Remember, when you uses this option to reset the decimal display format, it will affect EVERY cell that HAS NOT had it format reset with the "x.xx" command described in Section 4.1. _______ _____________________________________ | f6 | | f7 f8 f9 f0 | . . . | | | Cell White Low OKAY? | | Row Brw/Ylw High | | Col Magenta | | ALL Red | | Range Cyan | | Green | | Blue | Function keys F7 thru F9 work together to form a 'user defined option' that is executed when the F10 (OKAY?) key is pressed. The F7, F8, and F9 keys do not perform any action other than to set up the option. f7 - Defines a range of cell(s) that will be modified to display the DEFAULT COLOR, no matter what their current color setting is. f8 - Defines the NEW DEFAULT COLOR that will be used for ALL cells through the worksheet. That is, the color of cells outside the range defined by the F7 key may also be affected, since this command resets the DEFAULT COLOR that is used be all cells. f9 - Sets the display INTENSITY of the default color to High or Low. f0 - Executes the command option setup by function keys <F7> thru <F9>. ______________________________ Section 4.9 DISPLAY FORMATS ......... From the "Root Menu", press : f9 : :FORMATS: _______ _______ _______ _______ _______ | f1 | | f2 | | f3 | | f4 | | f5 | | DONE | | TIME | | Dec | | Sci | | Hex | . . . _______ _______ _______ _______ _______ | f6 | | f7 | | f8 | | f9 | | f0 | . . . | Oct | | Bin | | Comma | | $ | | % | f1 - Returns you to the Root Menu. f2 - Re-assigns the Function Keys to display numeric values using DATE and TIME formats. f3 - Sets a cell's numeric display mode to DECIMAL (default). For example, 255 would be displayed: 255.00 f4 - Sets a cell's numeric display mode to SCIENTIFIC NOTATION. For example, 255 would be displayed: 2.55e+02 f5 - Sets a cell's numeric display mode to HEXADECIMAL. For example, 255 would be displayed: 0xFF f6 - Sets a cell's numeric display mode to OCTAL. For example, 255 would be displayed: 0377 f7 - Sets a cell's numeric display mode to BINARY. For example, 255 would be displayed: 11111111 f8 - Sets a cell's numeric display mode to insert a comma after every third digit. For example, 2500 would be displayed: 2,500 f9 - Sets a cell's numeric display mode to CURRENCY. For example, 255 would be displayed: $255.00 f0 - Sets a cell's numeric display mode to PERCENT. For example, .25 would be displayed: 25% NOTE: Numbers can be entered, from the Input Line, in both hexadecimal and octal formats (in addition to the standard decimal format). To enter a hexadecimal number, precede it with a "0x" (e.g. 0xFF). Octal numbers can be entered by beginning them with a zero (0). JUST REMEMBER NOT TO BEING A STANDARD NUMBER WITH A ZERO. ________________________________________________ Section 4.9 (continued) DATE and TIME FORMATS Only curtain numbers can be is used to represent Date and Time values, which must conform to a specific set of requirements. These numbers are, in general, referred to as 'datecodes'. There general form is described below: YYYYDDD.HHMMSS Where: 'YYYY' is the year (0001 thru 9999) 'DDD' is the day of the year (1 to 365) 'HH' is the hour (00 thru 23) 'MM' is the minute (00 thru 59) 'SS' is the second (00 thru 59) Example: 1991005.123015 represents "January 5, 1991 12:30:15" Any number that adheres to this scheme can be displayed using one of the following Date or Time formats. Values, however, that violate this format will be displayed with a series of Exclamation Points (!!!). NOTE: You may have to adjust the column widths of some cells to see the full display. Unlike LABELS, datecode displays do not overlap empty cells. Instead, they are clipped to fit within the existing cell. ......... From the "Root Menu", press : f9 : :FORMATS: ......... : f2 : : TIME : _______ _______ _______ _______ _______ | f1 | | f2 | | f3 | | f4 | | f5 | | DONE | | Day | | Month | |Date&Tm| | Date | . . . _______ _______ _______ _______ _______ | f6 | | f7 | | f8 | | f9 | | f0 | . . . | m/d/y | | y/m/d | | hms PM| | hms | | | f1 - Returns you to the previous menu. f2 - For any valid datecode, displays the day of the week (Sunday, Monday, etc.) f3 - For any valid datecode, displays the month of the year (January, February, etc.) f4 - For any valid datecode, displays the Date and Time using the following format: October 25, 1991 03:58 PM f5 - For any valid datecode, displays the Date using the following format: October 25, 1991 f6 - For any valid datecode, displays the Date using the following format: 10/25/91 f7 - For any valid datecode, displays the Date using the following format: 91/10/91 f8 - For any valid datecode, displays the Time using the following format: 03:58:45 PM f9 - For any valid datecode, displays the Time using the following format: 15:58:45 (military time) _______________________ ______________________________________________/ Chapter 5 EDIT MODE From the Input Line, your editing capabilities are limited to erasing the last character typed with the <Back Space> key. In addition, there is no way of modifying the contents of existing cells - short of retyping them. Obviously, if you are working with long complicated formulas, this could be a tedious way of correcting errors or making changes. To provide you with more flexibility in this area, a special set of function key options are available, that will allow you to revise the contents of existing cells (when highlighted by the Cell Pointer). Specifically, you will be able to; 1) use the arrow keys to move the input cursor; 2) delete characters; 3) insert new characters; 4) search and replace patterns of characters; 5) 'capture' a series of characters to be re-inserted at a different location within the same or different entries; 6) add the contents of other cells to the cell being edited; 7) search for closing brackets; 8) display error messages; and 9) single step through a formula calculation, allowing you to check the intermediate results of each operation as the expression is evaluated. To enter the 'Edit Mode', move the Cell Pointer to the cell you wish to modify and press the <F2> function key TWICE from the Root Menu. The contents of that cell along with the Edit Mode Function Key Options should appear just above the Column Bar. You can now use the arrow keys to move the input cursor to any location within the entry. The <Up> arrow will move you immediately to the beginning of the entry, while the <Down> arrow will move you back to the end. There is also an 'insert' and 'overlay' character entry mode that allows you to insert new characters or overwrite existing ones. The <F1> function key will toggle you between them (refer to the f1 (INS) section below). You can use either the <Del> or <F2> keys to delete characters. Try entering the Edit Mode with the Cell Pointer positioned on an empty cell and experimenting with each of these options. Remember, you can use the <Esc> key at any time to abort. After you have made your changes, you can save them by pressing the <Enter> key. If a problem is detected, an error message will appear at the top of your screen and you will be returned to the Edit Mode to made the necessary changes. This differs from the way errors are handled from the Input Line. Here, no error messages are displayed. Instead, only a series of question marks (????) are be displayed in the cell(s) that detect a problem and you are allowed to continue as though nothing had happened. You can get a general idea of what went wrong, however, by moving the Cell Pointer to the cell containing the question marks and then entering the Edit Mode. This will cause an error message to be displayed that that will hopefully help you determine what when wrong. If you are unable to correct the problem from the Edit Mode, use the <Esc> key to exit (the contents that where displayed the last time you pressed the <Enter> key will be saved). NOTE: You can ABORT the 'Edit Mode' at any time by pressing the <Esc> key; however, all changes will be lost. ......... : f2 : From the "Root Menu", press F2 : EDIT : ......... : f2 : then press F2 again : Edit : _____ _____ _____ _____ _____ EDITING: |1 INS| |2 DEL| |3 RPL| |4 GO| |5 CUT| . . . f1 - Toggles between INSERT and OVERWRITE entry modes. When the insert identifier is displayed in capital letters (INS), you're in the 'overwrite' mode. This means that any character you type will be added to the end of the entry or will overwrite an existing character, depending on the cursor position. When the insert identifier is displayed with lower case letters (ins), you're in the 'insert' mode. In this case, new characters are placed between existing characters. f2 - This key DELETES the character at the current cursor position. It works just like the <Del> key on your keyboard. f3 - SEARCHES for a pattern of characters within the entry and REPLACES it with another pattern. You will be prompted to enter both of these strings. f4 - MOVES the Cell Pointer to another cell location WITHOUT exiting the Edit Mode. This allows you to view the contents of other cells; and, if you like, use the F7 (LBL) option to add them to the current entry. f5 CUTS (captures) a series of characters from the text that is currently being edited, placing them into the 'cut' buffer. This buffer can then be re-inserted at any location within the entry. To execute the command, press the <f5> key with the cursor positioned on the first character of the string you wish to capture. Additional characters will continue to be placed into this buffer while the cursor is moved to the right with the Right <Arrow> Key. Do not press any other key (other than the right arrow key) until AFTER you have TERMINATED THE 'CUT' OPERATION BY PRESSING THE <f5> KEY A SECOND TIME. The buffer's contents are preserved until the process is repeated (i.e. they are not lost when you exit the Edit Mode). This allows you to insert the buffer into other cells, as well. Note: the F6 (PUT) key is used to copy the contents of the 'cut' buffer back into the entry, starting at whatever cursor position you like. _____ _____ _____ _____ _____ . . . |6 PUT| |7 LBL| |8 ADR| |9 DBG| | [()]| f6 - Puts (inserts) the text saved in the 'CUT' buffer into the current entry (see the F5 'CUT' option). f7 - Inserts the 'formula' or 'label' of the cell that is highlighted by the Cell Pointer into the current entry. The F4 (GO) command can be used to move the Cell Pointer to the cell that contains statement you wish to capture. While in the Edit Mode, the <Home>, <End>, <Pg Up>, and <Pg Dn> Keys can also be used to move the Cell Pointer, one cell at a time. f8 - This option works like the F7 (LBL) command except that it inserts the 'Cell Address' of the cell that is highlighted by the Cell Pointer into the current entry. f9 - This command is used to test and debug 'formulas'. It allows you to see exactly how an expression is being evaluated - one step at a time. After each operation, it will pause and display the value calculated or substituted. Press any key to continue to the next operation. f0 - With the cursor placed on any square bracket [] or parenthesis (), this option will cause the cursor to jump to its CLOSING bracket. This command also works with the ?:; (arithmetic-if) delimiters. ____________________________ _________________________________________/ Chapter 6 TECHNICAL INFO This chapter is designed to provide you with a broader understanding of the more important topics covered thus far. Every attempt has been made to keep each of these sections as short and to the point as possible. The first six contain most of the what you'll need to use REBEL effectively. It's highly recommended that you read them. The others can be read as you feel the subject matter applies. While each section tends to build on the information that precedes it, it is not necessary to read them in any particular order. _________________________ Section 6.1 CELL TYPES A FORMULA, LABEL, or VARIABLE LABEL cell is created each time an entry is made (limited to 255 characters) from the Input Line. The specific type of cell created, however, depends on whether the entry is interpreted as a mathematical expression (a formula) or a string of characters (a label). For the most part, this is done automatically by the spreadsheet, which will attempt to make an educated guess based on the first character of your entry. It is important that the correct decision is made (and in most cases it is), since the cell type governs the way in which a cell's data can be used by other cells. FORMULA CELLS are created whenever a mathematical expression (formula) is entered at the Input Line. This can be anything from a simple number to a complex set of arithmetic operations. To be interpreted as such, it must begin with one of the following characters: + - . [ ( 0 1 2 3 4 5 6 7 8 9 There are, however, times when an otherwise valid expression may not begin with one of the above characters (i.e. expressions that begin with function calls). In these situations, you must FORCE the spreadsheet to interpret your entry as a 'formula' by beginning it with a plus sign (+). For example, the formula "sqrt(16)" should be entered as "+sqrt(16)" to prevent it from being interpreted as a 'label'. The plus sign (+) will not affect the results. LABEL CELLS are created whenever an entry is made that DOES NOT begin with one of the characters that denotes a formula. Labels consist of a string of characters that appear on the screen exactly as they are typed from the Input Line. The spreadsheet makes no attempt to do anything special with these entries. Just as with formulas, however, there are cases when a label must begin with a character that can cause some confusion. For example, the string "1st of January" begins with a '1'. If typed in as it stands, the spreadsheet will look at the first character and think: "this is a formula", and it will create a FORMULA CELL to store it in. This, of course, will produce an error when the spreadsheet attempts to calculate it's value! That is, what does "1st of January" mean mathematically? It is easy to tell when this has happened by the question marks (?????) which are displayed in the cell instead of the label you expected. The solution is simple - just begin the label with a single quote ('). This will FORCE the spreadsheet to view the entry as a label, rather than a formula. The leading single quote will not appear when the string is displayed. A label that is longer than the column width of the cells that it's stored in will overlap up to to 63 adjacent empty cells before its display is finally terminated. VARIABLE LABEL CELLS are created ONLY when an entry is made that begins with a double quote ("). Unlike the single quote ('), which is often not necessary for standard labels, the double quote is REQUIRED for variable labels. Like standard labels, variable labels display everything in the character string (just as its appears) - except for Cell Addresses. When a Cell Address (e.g. [1,2]) is encountered, the contents of THAT cell is displayed, rather than the Cell Address itself. For example, if cell [1,2] contains the string "red", an entry such as this: "The car is [1,2]. would be displayed as: The car is red. An UNDEFINED CELL is automatically created by the spreadsheet whenever an 'empty' cell is referenced by another cell's formula. You can not create these cells yourself. They are easily identified by the long underscore (________) that appears in the cell's display on the worksheet; and, can be eliminated only after all references to them have been removed. UNDEFINED (empty) cells will return zero (0) when referenced, which can often produce questionable results. When this occurs, a series of question marks (?????) will appear in the display of the cell that contains the address. To correct it, simply locate the UNDEFINED cell using the (~) Cell Trace Command (described in Chapter 7) and enter whatever value the formula expects. NOTE: Empty cells are not 'typed' and thus, consume no memory. _____________________________ Section 6.2 CELL ADDRESSES A CELL ADDRESS (e.g. [row,column]) is simply a way of referencing another cell's value within a formula. Not only do Cell Addresses allow you to access the values of other cells, but they also cause the formulas which use them to be recalculated when the values of the cells they reference are changed. Cell Addresses are composed of four (4) parts or fields (the Row, Column, Array Element, and Worksheet Level) separated by commas and enclosed within square brackets []. Of these, only the 'Row' and 'Column' fields that define the cell's position on the worksheet are required. REQUIRED________ ________________ OPTIONAL | | | | [Row,Column,Element,Worksheet] | | only needed when _______| |____ only needed when accessing accessing arrays cells at a different level The ARRAY ELEMENT (the 3rd field) defaults to zero (0) if not used, causing the Cell Address to reference the value displayed on the screen. This value is the result of the expression or formula associated with the cell. When a FORMULA cell is first created, there is NO array space assigned to it. As a result, this field has no practical meaning until the area has been setup by the user. If defined, however, the Array Element field can be used to reference up to 8000 values assigned to the cell. For LABEL cells, the field is used to address individual characters within the label itself. The size of the string, therefore, governs the largest maximum element number that can be accessed. For additional information, see Section 6.5 on Arrays. The 4th, WORKSHEET LEVEL, field also defaults to the zero (0) when left blank. Not entering a value in this field tells the spreadsheet to use the level of the worksheet that contains the formula. This is what allows a Cell Address to be copied to virtually any level without requiring it to be modified. When used, however, the field extends to you the ability to access cells from any of the four (4) worksheet levels. Keep in mind, the Cell Addresses that uses this field may need to be modified if the worksheet that contains them is later loaded at a different level. It's always best to leave this field BLANK when accessing cells at the same worksheet level (refer to Section 6.6 - Multiple Worksheets). Examples: [1,2] ==> Makes a reference to the 'value' in the cell located at row 1, column 2 of the worksheet that contains the the formula. [1,2,0] ==> same as above. [1,2,,4] ==> Makes an cell reference to the value in the cell located at row 1, column 2 of worksheet level 4. [1,2,3] ==> References the value in the 3rd array element of the cell located at row 1, column 2 of the worksheet that contains the formula. NOTE: The Row, Column, Array, and Worksheet fields can themselves be Cell Addresses. _______________________ Section 6.3 OPERATORS The operators provided by this spreadsheet are patterned after those defined in the 'C' Programming Language - with a few minor exceptions. The precedence of each operator is listed in following table from highest to lowest priority. Parenthesis (), of course, can be used at any time to alter the order in which operations are performed. UNARY OPERATORS --------------------------------------------------------------------- Op Name Use --> Result Description --------------------------------------------------------------------- ! Logical NOT !0 --> 1 converts 0 (F) to 1 (True) !5 --> 0 converts 5 (T) to 0 (False) - Unary Minus -(-5) --> 5 negates a negative 5 + Unary Plus +(-5) --> -5 has no affect ARITHMETIC OPERATORS --------------------------------------------------------------------- Op Name Use --> Result Description --------------------------------------------------------------------- ** Exponentiation 4**2 --> 16 4 raised to the power of 2 * Multiplication 4*2 --> 8 4 multiplied by 2 / Division 4/2 --> 2 4 divided by 2 % Modulo 5%3 --> 2 remainder of 5 divided by 3 + Addition 5+3 --> 8 5 added to 3 - Subtraction 5-1 --> 4 5 minus 1 NOTE: When using the modulo (%) operator, the values of both expressions are temporarily converted to whole numbers before the operation is performed. The result is always a whole number. BITWISE (SHIFT) OPERATORS --------------------------------------------------------------------- Op Name Use --> Result Description --------------------------------------------------------------------- << Left Shift 2<<3 --> 16 shift the bits that represent the number 2 3 positions to the left >> Right Shift 16>>3 --> 2 shift the bits that represent the number 2 3 positions to the left NOTE: The values of both expressions are temporarily converted to whole numbers before these operations are performed. The result is always a whole number. RELATIONAL OPERATORS --------------------------------------------------------------------- Op Name Use --> Result Description --------------------------------------------------------------------- == Equal to 4==5 --> 0 (F) 4 equals 5 (FALSE or 0) != Not Equal to 2!=3 --> 1 (T) 2 does not equal 3 (TRUE) <= Less or Equal 2<=2 --> 1 (T) 2 is less than or equal to 2 (TRUE or 1) >= Greater or Equal 2>=4 --> 0 (F) 2 is not greater than or equal to 4 (FALSE or 0) < Less than 2<3 --> 1 (T) 2 is less than 3 (TRUE) > Greater than 3>2 --> 1 (T) 3 is greater than 2 (TRUE) BITWISE OPERATORS --------------------------------------------------------------------- Op Name Use --> Result Description --------------------------------------------------------------------- & Bitwise AND 1&3 --> 1 bits are set where corresponding bits of both numbers are the same | Bitwise OR 1|3 --> 3 bits are set when either number has a corresponding bit set NOTE: The values of both expressions are temporarily converted to whole numbers before these operations are performed. The result is always a whole number. LOGICAL OPERATORS --------------------------------------------------------------------- Op Name Use --> Result Description --------------------------------------------------------------------- && Logical AND (1<2)&&0 --> 0(F) if (1<2) is TRUE AND 0 is TRUE (0 is FALSE) then the result is TRUE (1) || Logical OR (1<2)||0 --> 1(T) if (1<2) is TRUE OR 0 is TRUE then the result is TRUE (1) ASSIGNMENT OPERATORS --------------------------------------------------------------------- Op Name Use Description --------------------------------------------------------------------- IMPORTANT! Assignments can only be made to ARRAY ELEMENTS or the SPECIAL REGISTERS associated to Range Operations. = Assignment [1,1,6] = 5 assigns the value (5) to the sixth array element of cell [1,1] += addition x += 5 same as x = x + 5 -= subtraction x -= 5 same as x = x - 5 *= multiplication x *= 5 same as x = x * 5 /= division x /= 5 same as x = x / 5 %= modulo x %= 5 (see note) same as x = x % 5 <<= shift left x <<=5 (see note) same as x = x << 5 >>= shift right x >>=5 (see note) same as x = x >> 5 &= AND (bitwise) x &= 5 (see note) same as x = x & 5 |= OR (bitwise) x |= 5 (see note) same as x = x | 5 NOTE: The Compound Assignments (% << >> & and |) will temporarily convert both operands to whole numbers before computing the results. The result is always returned as a whole number. Example: 1) [1,2,1] = 5 (legal) 2) [1,2] = 5 (illegal) 3) [1,2,1] += 5 (takes the value in [1,2,1], adds 5 to it and places the results back into [1,2,1]) TERNARY CONDITIONAL OPERATOR --------------------------------------------------------------------- Op Name Use --> Result Description --------------------------------------------------------------------- ?:; Arithmetic (2>1)?5:4; --> 5 IF 2 is greater than 1 if-then-else THEN(?) 5 ELSE(:) 4 ____ (optional assignment to an array element) | | (IF) (THEN) (ELSE) [r,c,a] = (Expression_1) ? Expression_2 : Expression_3 ; | | | |_________(REQUIRED)__________| IF: 'Expression_1' results in a non-zero value THEN: The expression that follows the question mark (?) is evaluated (i.e. 'Expression_2') and whatever follows the colon (:) is ignored. ELSE: The expression that follows the colon (:) is evaluated (i.e. 'Expression_3'). Example 1 ARITHMETIC IF-THEN-ELSE IF the contents of cell [1,1] is greater than or equal to the contents of cell [2,2]: THEN multiply the contents of cell [3,3] by 2; ELSE multiple the contents of [3,3] by 4. AND assign the results of Array Element 6 of cell [1,2]. e1 e2 e3 [1,2,6] = ([1,1]>=[2,2]) ? [3,3]*2 : [3,3]*4; or ([1,1]>=[2,2]) ? [1,2,6]=[3,3]*2 : [1,2,6]=[3,3]*4; _______________________ Section 6.4 FORMULAS A 'formula' is the expression (or the set of arithmetic operations) that is associated with a FORMULA cell when it is first created. It is recalculated each time a change is made to one of the cells it addresses. Since any cell can have a formula, which in turn can reference the results of other cells, a whole progression of calculations can be performed based on a single change. Any given cell can have only one formula assigned to it. When evaluated, the result is ALWAYS stored at Element position '0' of the Cell Address ([1,2,0] or by default [1,2]), which is reserved for this purpose. Other Element positions (i.e. those associated with the optional ARRAY AREA) can not have 'formulas' assigned to them. This is a subtle, but important, distinction between Element position '0' and those that range between 1 and 8000, which is discussed in more detail in the next section. ____________________ Section 6.5 ARRAYS Arrays have two unique properties: 1) they allow you to store large amounts of information with very little overhead; and 2) their values can be set with the assignment operators (=, +=, -=, etc.). Once a FORMULA CELL has been created, an ARRAY AREA of upto 8000 values can be defined and attached to it (see Section 4.1 "Array" for more information on how to do this). These values can then be referenced by including the Array Element field in the Cell Address. For example, the Cell Address "[1,2,7]" would reference the 7th array value assigned to the cell at row 1, column 2. An important point to remember is that element position '0' IS NOT part of the ARRAY AREA. This position is reserved for the result of the cell's formula and is assigned to it when the cell is first created. The ARRAY AREA, on the other hand, is defined by the user and can only be used to store static values or numbers. Referencing elements outside of the array area (by using element numbers larger than the maximum size of the array space defined for the cell) will always return a value of zero (0). Because formulas are not and cannot be associated with the values stored in the ARRAY AREA, modifications to these values will not trigger recalculations of any cell that references them; so, exercise caution when referencing arrays that might have their values changed (see Section 6.11 "Forced Recalculations" for ways around this). Generally speaking, arrays should be reserved for relatively static data. If necessary, however, array values can modified from either the Input Line (see Chapter 7 "Set Element") or programmatically with the assignment operators (see Section 4.3 - Assignment Operators). When using assignment operators, the Array Element field of the Cell Address MUST be included and it MUST be greater than or equal to 1 and less than or equal to the maximum array space defined for the cell. You can NOT uses an assignment operator to change the value at element position '0'. Remember, this position is reserved for the cell's formula and is not part of the array space. This might best be explained with an example. Let's say you enter the expression "2+2" in cell [1,2]. In this case, the value 4 would be stored at element position '0' and would be displayed on the screen. If you were then to type the expression "[1,2,0]=3" at another cell location, you would in effect be trying to tell cell [1,2] that the result of its formula "2+2" equals 3 - not 4! If, on the other hand, you had entered "[1,2,1]=3" it would have been perfectly legal, since an array element simply stores a value and is not the result of a pre-assigned formula. NOTE: While an Array Area can be assigned to a cell at any time, it is best to do it (especially for large ones) as soon as possible after starting up the program - before the computer's memory becomes fragmented. You should also be careful when moving cells that contain arrays, as other cells that contain formulas that reference the array elements of these cells will not be adjusted to the new location. IMPORTANT! As previously mentioned, arrays can only be assigned to FORMULA cells. This, however, does not mean that the Array Element field of cells that contain LABELS is not used. For LABELS, this field is used to address specific characters within a label. For example, if cell [1,3] contained the label "Hello world!", [1,3,1] would address the first letter 'H' (or more specifically, the ASCII value for 'H' which is 72). The assignment operators can also be used to reset the characters of any label to any ASCII value ranging between 32 and 255. Characters greater than 125, however, will not be saved when backing up your worksheet. Example 1 REFERENCING AN ARRAY ELEMENT [1,2,7] * 10 Example 2 ASSIGNING A VALUE TO AN ARRAY ELEMENT [1,2,7] = 25.0 Example 3 LOAD AN ARRAY AREA Load a 400 element array, defined at cell [1,50], with a range of values that appear between cells [1,1] and [20,20] on the worksheet (see Section 6.7 "Range Operations" for details on how to interpret this expression). [1,1 ^ 20,20; {[1,50,[#]]=[@]}] NOTE: The array area for cell [1,50] must have been defined prior to making these assignments. __________________________________ Section 6.6 MULTIPLE WORKSHEETS Up to four (4) worksheets can be loaded and accessed at any given time. You can move between them with the Forward Slash (/) 'Goto' Command described later on in Chapter 7. The number between the angle brackets <>, located at the intersection of the Row and Column Bars, indicates the worksheet level of each window. Formulas can be written that reference cell values from the same worksheet by leaving the Worksheet Level field BLANK or from worksheets loaded at different levels by including the Worksheet Level field in the Cell Address. For example: [1,2] ==> Addresses the cell at row 1, column 2 of whatever level the formula that contains this reference is loaded at (1 thru 4). [1,2,,3] ==> Addresses the cell at row 1, column 2, worksheet 3 (this address will always access the cell at level 3 - no matter what level it is loaded at) [1,2,0,3] ==> same as above Normally, it IS NOT a good idea to include the 'Worksheet Level' field in a Cell Address that reference a cells at the SAME level. First it is not necessary; and second, it can cause problems if you later copy the cell's formula to another level. When this field is left blank or is set to zero (0), it's value will automatically be reset to whatever level it's loaded at! This allows formulas that contain Cell Addresses to be copied to virtually any level without modifying them. Remember, whenever the Worksheet Level is specified (hardcoded) in a Cell Address, that Cell Address will ALWAYS access that particular cell - no matter where the formula that contains the address is later copied. For example, if a formula entered at level 1 contains a hardcoded cell reference to another cell at level 1 (e.g. [2,3,,1]), it will still reference the same cell (at level 1) - even if the worksheet is later loaded at level 3. So, when in doubt, do not use the 'Worksheet Level' field, since 99 percent of the time your formulas will be referencing other cells in the same worksheet. _______________________________ Section 6.7 RANGE OPERATIONS Unlike most spreadsheets, that restrict a range to defining a block of cells which can be used in function calls, this spreadsheet allows a range to be used in much the same way as a Cell Address. That is, a range (or Range Operation) IS AN EXPRESSION that returns a value - not just a block of cells. What makes this possible, is the ability to associate one or more formulas with the range of cells. While computing the value to be returned by a Range Operation, each of these formulas is evaluated once, except when surrounded by curly braces {}; in which case, the expression is evaluated once for each FORMULA cell in the range. This allows you to move though a whole series of cells, performing a variety of operations as you go. The following is a diagram of how Range Operations are organized: defines formulas surrounded by lower right curly braces {} are corner ___ executed once for defines | _ each cell in the range upper left ______ |___ | corner | | | | | [row,col ^ row,col ; f; {f}; f;] | | |_______| required ____| | |____ formulas not | surrounded by required between _____| {} are evaluated each formula only once [ ] - By definition, Range Operations MUST be enclosed within square brackets. ^ - The (^) symbol is a required to separate the cells which define the upper left and lower right corners of the range of cells. f - The (f) in the above diagram represents an expression (formula) that is evaluated once when it is first encountered. {f} - Curly brackets {} act like a FOR or DO loop that forces the Range Operation to access the value of each FORMULA cell within the range (beginning at the upper left corner and moving in a left-right direction). The formula within the brackets is executed once as each cell is encountered. Only one formula can be enclosed within each set of brackets. ; - Each formula must be separated by a semicolon. There are also seven special 'registers' that are reserved especially for Range Operations. These registers are designed to track critical values that may change as the expressions (associated with the Range Operations) are evaluated. Each is represented by a special character enclosed within square brackets (i.e. [$], [#], etc.) and is described below. [$] - Contains the INTERMEDIATE and FINAL results of a Range Operation. It acts as an accumulator, whenever an expression is surrounded by curly braces {}, storing the value produced by the expression after it is evaluated once for each cell within the range. By default, this register is initialized to '0' prior to evaluating an expression surrounded by curly braces. [@] - Whenever an expression is enclosed within curly brackets {}, it will be evaluated once for every FORMULA cell within the range. The [@] register acts like a variable Cell Address that contains the VALUE of the cell currently being accessed by the Range Operation as it moves from one cell to the next. [#] - This register is a COUNTER. It is initialized to one (1) at the start of any expression that is surrounded by curly brackets {}. Its value is incremented by one each time the Range Operation moves to the next FORMULA cell. [*] - This register has no predefined function. It can be used with assignment operations to hold the intermediate results of any calculation. [-] - Whenever an expression is enclosed within curly brackets {}, it will be evaluated once for every FORMULA cell within the range. The [-] register contains the ROW number of the cell currently being accessed by the Range Operation. This is the same cell whose value is currently in the [@] register. [|] - This register contains the COLUMN number of the cell currently being accessed by the Range Operation. [%] - This register contains the WORKSHEET LEVEL number of the cell currently being accessed by the Range Operation. Example 1 COUNT THE CELLS Count the number of FORMULA cells within a range. (Empty cells and cells that contain Labels are skipped.) [1,1 ^ 12,12] NOTE: By default, a Range Operation that contains no formulas will return the total number of FORMULA cells found within the range. A more literal way of doing the same thing would be: [1,1 ^ 12,12; [$]=[#] ] REMEMBER: The result of any expression, after it has been evaluated (in this case: [#]) will automatically be placed in the [$] register. As a result, the [$]=[#] assignment really isn't necessary and the above Range Operation could have been shortened to look like this: [1,1 ^ 12,12; [#] ] Example 2 SUM THE CELL WITHIN A RANGE ___ accumulated results | [1,1 ^ 12,12; {[$]+[@]} ] |___ current cell value Example 3 SUM THE VALUES IN AN ARRAY ___ accumulated results | [2,3,1 ^ 2,3,12; {[$]+[@]} ] | | |___ current cell value |________|___ array element numbers NOTE: Range Operations can only be performed on arrays if the Row, Column, and Worksheet Level are equal. Example 4 COMPUTE AVERAGE VALUE WITHIN A RANGE accumulated results___ | [1,1 ^ 12,12; {[$]+[@]}; [$]/[#] ] | final cell count__| REMEMBER: Only FORMULA cells are visible to a Range Operation. Example 5 RETURN THE LARGEST NUMBER Find the largest positive number within the range bounded by cell [10,10] in the upper left hand corner and cell [20,20] in the lower right corner. [10,10^20,20; {([@]>[$]) ?[@] :[$];} ] Example 6 STANDARD DEVIATION Compute the Standard Deviation for a range of cells between [1,1] and [5,1] that contain the following values: 3, 3, 4, 7, and 8, respectively. save average ____ | [1,1 ^ 5,1; {[$]+[@]}; [*]=[$]/[#]; {[$]+([@]-[*])**2}; sqrt([$])/([#]-1))] Example 7 SEARCH A RANGE OF VALUES Perform a Table Search between cells [1,1] and [5,1] for the first value greater than 4. If found, return the value located one column to the right of it. (This is similar to the @VLOOKUP function used by many spreadsheets). [1,1 ^ 5,1; {[*] = ([@]>4) ?[[-],[|]+1] :[*];}; [$]=[*]] ____________________________________ Section 6.8 DIRECT CELL ADDRESSES A Cell Address (i.e. [Row,Col,Element,Level]) makes a 'direct' cell reference to the value in another cell when: NONE OF THE FIELDS USED WITHIN THE CELL ADDRESS ARE VARIABLE. That is, when the specific cell that is being referenced by a Cell Address (within a formula) can never be changed as the result of an update made somewhere else in the spreadsheet, it is termed a 'direct' cell reference. For example, "[1,2]" makes a direct cell reference to the cell at row 1 column 2, since it's Row or Column fields can not vary. Direct cell references have one very important property - THEY CAN CAUSE THE FORMULAS THAT CONTAIN THEM TO BE RECOMPUTED WHENEVER THE VALUE OF THE CELL BEING ADDRESSED IS CHANGED. This is opposed to expressions that contain 'indirect' cell references, which are not recomputed under similar circumstances. The reason for this is that the spreadsheet is able to keep track of 'direct' cell references, but not 'indirect' references - due to their dynamic nature. (see Section 6.9 - Indirect Cell References) ______________________________________ Section 6.9 INDIRECT CELL ADDRESSES Because the 'Row', 'Column', 'Array', and 'Worksheet' fields are themselves expressions, their values could vary based on changes made to the spreadsheet. If this should occur, it would have the affect of changing the actual cell being addressed! This is called 'indirect' cell addressing; and, it can be quite useful at times. Say, for example, cell [1,1] contains the value 10 and cell [2,2] contains the value 20. A Cell Address can now be written that uses the values in both of these cells as its 'row' and 'column' number fields; thereby referencing cell [10,20] - INDIRECTLY! In other words, the cell that is actually being addressed depends on the contents of cells [1,1] and [2,2]. It might look something like this: __________________ Indirect Reference | | to cell [10,20] [ [1,1] , [2,2] ] The ROW is the value | | The COLUMN is the value in cell [1,1]________| |____ value in cell [2,2] NOTE: In the above example, the references to cells [1,1] and [2,2] are 'direct' cell references. Notice that as the values in cells [1,1] and/or [2,2] change, the 'address' of the cell being referenced by the above expression will also change. This can be a very powerful means of accessing values within tables, depending on the results of other calculations. There is, however, one significant trade off you must make when using 'indirect' cell references: CHANGES MADE TO THE VALUE OF ANY CELL THAT IS REFERENCED 'INDIRECTLY' WILL NOT CAUSE THE FORMULA CONTAINING THAT REFERENCE TO BE RECALCULATED. The only way to get around this particular drawback is to set a flag that will FORCE the cell, containing the reference, to be recalculated each time ANY change is made to the spreadsheet (see Section 6.11 - Forced Recalculations). _____________________________________________ Section 6.10 WHEN FORMULAS ARE RECALCULATED Sometimes it is difficult to tell when a change to the value of a cell will affect the value in another cell. If you are having trouble with this, try to remember the following rule: A DIRECT CELL REFERENCE, within a formula, will cause THAT formula to be recalculated each time the value of the cell (being referenced) is changed. NOTE: There is one exception to this rule - Range Operations. While Range Operations do not make 'direct' cell references to the range of the cells that can affect them, they do take on one of the characteristics of 'direct' cell references - FORMULAS THAT CONTAIN 'RANGE OPERATIONS' WILL BE UPDATED EACH TIME THE VALUE OF A CELL WITHIN THE RANGE IS CHANGED. If you are ever in doubt as to which cells cause a specific formula to be recomputed, you can use the (~) Cell Trace Command to find out (see Chapter 7). By placing the Cell Pointer over the cell in question and then execute this command from the Input Line, you will be shown all of the cells that it makes a DIRECT REFERENCE to. These are the cells that will cause the formula to be recomputed if their values are changed. In addition, you will be shown the cells that use the value of the cell in question. ______________________________________________ Section 6.11 FORCING FORMULAS BE RECALCULATE Occasionally, you may run into a case were a cell's formula is not recalculate when the value of one of the cells that it references is changed. For example, if a cell makes an INDIRECT CELL REFERENCE to another cell, its formula will NOT be recomputed when the value of this (indirectly) referenced cell is changed. When it is absolutely necessary that a cell's value reflect such changes, a special flag can be set that will FORCE the cell's formula to be recalculated whenever ANY change is made to the spreadsheet. Section 4.1 "+ReCalc" describes how to do this by setting up and executing the following command: "+ReCalc Cell OKAY?". This flag should be used sparingly, however, as it defeats the "Minimal Re-Calc" capabilities of the spreadsheet. __________________________________________ Section 6.12 OPTIMIZING YOUR WORKSHEET Generally speaking, there are several things you can do to optimize your worksheet: 1) PREVENT complex formulas from being recomputed until their results are needed with the "+NoCalc" setting (see Section 4.1); 2) LIMIT the number of 'indirect' cell references - 'direct' cell references are faster; 3) KEEP the 'dRange' default setting set to 1 (refer to Section 4.8) to reduce the number of times a formula that contains a recursive Range Operation will be recalculate. If a cell contains a complicated formula that takes a long time to re- calculate, you can speed up the spreadsheet by blocking the calculation until you the specifically request that it be made. Section 4.1 describes how to do this by setting up and executing the following command: "+NoCalc Cell OKAY?". It's up to you, however, to remember when and where you have used this flag on a cell! To minimize the number of calculations that must be performed after a change has been made to the spreadsheet, REBEL will only re-compute the cells that are affected by the change. This is often referred to as "minimal recalc". There are, however, certain cases in which this strategy can be more of a disadvantage than an advantage. Take, for example, cell [25,25] whose formula contains a 'Range Operation' that sums all of the cells bounded by cell [1,1] (in the upper left corner) and cell [20,20] (in the lower left corner). The expression might look something like this: [1,1 ^ 20,20; {[$]+[@]}]. Here's the problem, lets say that EACH cell within that range is affected by a change made to cell [45,45] (i.e. all cells within this range make a 'direct' reference to this cell). Now, imagine the following scenario. Suppose you change the value at cell [45,45]. What happens? Each of the cells within the range will be recomputed; AND each time this happens it will triggers the formula in cell [25,25] to be recomputed. As a result, the Range Operation in cell [25,25] will be needlessly recomputed 400 times (once for each cell in the range); when in reality, the Range Operation only needed to be recomputed once at the end - after all of the changes had been made. This, of course, is a worst case and it is also easy to handle (if you recognize that it's happening) with a special setting that limits the number of times a Range Operation can be be recalculated as the result of a single change made to the worksheet (see Section 4.8 - "dRange"). Another type of Range Operation that can cause problems is one that includes itself WITHIN ITS OWN RANGE! That is, a Range Operation that makes a circular reference to itself, causing the cell's formula to be recalculated - indefinitely. Once again, the "dRange" setting can be used to control this situation. ___________________________________ Section 6.13 MISCELLANEOUS TOPICS ENTERING NUMBERS (from the Input Line) Numbers can be entered from the Input Line in several different ways, in addition to the standard decimal format. For example: 1) The numeric value of any ASCII character can be entered by enclosing the character within single quotes. Note, if the single quote is the first character in the expression, be sure the precede it with a plus sign (+). This will prevent the expression from being interpreted as a Label. 'A' ===> 65 'B' ===> 66 '!' ===> 33 2) Octal numbers can be entered by beginning them with a zero: 040 ===> 32 0377 ===> 255 3) Hexadecimal numbers can be entered by preceding them with a "0x": 0xFF ===> 255 IMPORTANT! To avoid confusion, remember to NEVER being a decimal number with a zero (unless, of course, the number is zero)! SWITCHES Two switch can be used when starting up REBEL: 1) The -s switch can be used to create a backup file, whenever a worksheet is load. This file will be an exact copy of the original worksheet, but will have a .RB0 extension. 2) The -b switch can be used to startup REBEL in black and white mode; otherwise, the program will be be started in the color mode. RESERVED and WILDCARD CHARACTERS The tilde (~) character is reserved by REBEL. As a result, this character can not be used within text labels. This character is, however, used as a wildcard character in several commands (refer to the 'Search' command in the next Chapter, and the 'Load' and 'ListDir' commands described in Section 4.3. ________________________ _____________________________________________/ Chapter 7 SHORT CUTS The following 'Short Cut' commands are designed to be executed from the Input Line (below the Function Key Menu). They do not perform critical tasks; although, you may find them useful time savers. Each of these commands (with the exception of the <Tab> Command) must be followed by the <Enter> key before it is executed. NOTE! Because the backslash (\), period (.), and equal sign (=) are used to trigger Short Cut Commands, you CANNOT begin a LABEL with one of these characters without first preceding it with a single quote ('). Command Description CHANGE WINDOWS <Tab> Pressing the <Tab> key at the Input Line prompt, will cause the Cell Pointer to move to another window (if one exists). SEARCH (\...) A backslash (\) followed by any pattern of characters will cause the Cell Pointer to move to the first cell the contains an occurrence of that pattern. If the Cell Pointer is positioned on an 'empty' cell, the search will begin at the top of the worksheet. If the Cell Pointer is positioned on an 'occupied' cell, the search will begin from that point. The pattern does not have to be re-entered to make repeated searches. Simply type a backslash (\) followed by the <Enter> key after you have entered the pattern the first time. The wildcard character (~) can also be included within the search pattern. GOTO CELL (/) A Forward Slash (/) will produce a series of prompts that will request the Row, Column, and Worksheet Level to move the Cell Pointer to. The value in the square brackets [] of each of these prompts is the default answer that will be used if you press the <Enter> key with no other input. This command can also be used with prompts that require you to move the Cell Pointer to define a Range or Target cell. DISPLAY ELEMENT (=e) An Equal Sign (=) followed by an array element number (e.g. =5) can be used to display the value of any array position of the cell highlighted by the Cell Pointer. The display appears at the Input Line and can be removed by pressing any key. For example, "=1" will display the value of the first array element. Note: "=0" (or simply "=") will always return the value associated with the cell's formula. The following variations of this command are also acceptable: =* Displays all of the values stored in a cell's array area - one at a time. =(5,9) Displays elements 5 thru 9 SET ELEMENT (=e<value) An Equal sign (=) followed by an array element number (greater than 0), then a Less Than sign (<), and finally a value will cause that value (to the right of the '<' sign) to be loaded at the designated array position. For example, "=7<25" will load the value 25 into element position 7 of the cell that is highlighted by the Cell Pointer. The following variations of this command may also be used: =*<0 Set the values of all elements to 0 =(5,9)<0 Set elements 5 thru 9 to 0 CELL TRACE (~) A Tilde (~), entered at the Input Line, will display the current attribute settings for the cell highlighted by the Cell Pointer. It will also 'trace' all of the 'direct' cell references made within the formula; and, all of the other cells within the spreadsheet that use this cell's value. FUNCTION KEYS (~n) A Tilde (~) followed by a number (ranging between 1 and 10) is treated just as though the equivalent Function Key was pressed (i.e. pressing Function Key <F1> has the same affect as entering ~1). This command is useful when a keyboard has fewer than than the 10 required Function Keys. ________________________ _____________________________________________/ Chapter 8 FUNCTIONS Index to Functions abs(x) . . . . . . 9.1.0 MATH . . . . . . absolute value acos(x) . . . . . 9.2.0 TRIG . . . . . . arccosine asin(x) . . . . . 9.2.0 TRIG . . . . . . arcsine atan(x) . . . . 9.2.0 TRIG . . . . . . arctangent atan2(x,y) . . . . 9.2.0 TRIG . . . . . . arctangent atof . . . . 9.6.0 STRING . . . . . string to number ceil(x) . . . . 9.1.0 MATH . . . . . . ceiling col(x) . . . . 9.7.0 MISC . . . . . . column-offset cos(x) . . . . 9.2.0 TRIG . . . . . . cosine cotan(x) . . . . 9.2.0 TRIG . . . . . . cotangent cterm(l,f,p) . . . 9.3.0 FINANCE . . . . term (lump sum) date(y,m,d) . . . 9.4.0 DATE . . . . . . datecode day(dc) . . . . 9.4.0 DATE . . . . . . day-of-the-month dpm(y,m) . . . . 9.4.0 DATE . . . . . . days in the month dpy(y) . . . . 9.4.0 DATE . . . . . . days in the year exp . . . . 9.1.0 MATH . . . . . . exponential floor . . . . 9.1.0 MATH . . . . . . floor frac . . . . 9.1.0 MATH . . . . . . fractional value fv . . . . 9.3.0 FINANCE . . . . future value hour . . . . 9.5.0 TIME. . . . . .. hour index . . . . 9.6.0 STRING . . . . . locate character int . . . . 9.1.0 MATH . . . . . . integer value log10 . . . . 9.1.0 MATE . . . . . . base 10 log ln . . . . 9.1.0 MATH . . . . . . natural log lvl . . . . 9.7.0 MISC . . . . . . worksheet level offset minute . . . . 9.5.0 TIME . . . . . . minute month . . . . 9.4.0 DATE . . . . . . month now . . . . 9.5.0 TIME . . . . . . current date and time nxdate . . . . 9.4.0 DATE . . . . . . next date nxtime . . . . 9.5.0 TIME . . . . . . next time pmt . . . . 9.3.0 FINANCE . . . . payment pv . . . . 9.3.0 FINANCE . . . . present value rate . . . . 9.3.0 FINANCE . . . . interest rate rnd . . . . 9.1.0 MATH . . . . . . rounds value row . . . . 9.7.0 MISC . . . . . . row offset second . . . . 9.5.0 TIME . . . . . . second sin . . . . 9.2.0 TRIG . . . . . . sine sqrt . . . . 9.1.0 MATH . . . . . . square root strcat . . . . 9.6.0 STRING . . . . . string concatenate strcmp . . . . 9.6.0 STRING . . . . . string compare strcpy . . . . 9.6.0 STRING . . . . . string copy strlen . . . . 9.6.0 STRING . . . . . string length strncat . . . . 9.6.0 STRING . . . . . concatenate n chars strncmp . . . . 9.6.0 STRING . . . . . compare n chars strncpy . . . . 9.6.0 STRING . . . . . copy n chars tan . . . . 9.2.0 TRIG . . . . . . tangent term . . . . 9.3.0 FINANCE . . . . interest rate time . . . . 9.5.0 TIME . . . . . . time tmdif . . . . 9.5.0 DATE . . . . . . diff btw two times wkday . . . . 9.4.0 DATE . . . . . . day-of-the-week year . . . . 9.4.0 DATE . . . . . . year Section 8.1.0 MATH FUNCTIONS _______________________________________________________________ abs (x) Returns the absolute value of any expression 'x'. _______________________________________________________________ ceil (x) Returns the smallest integral value that is greater than or equal to the value produced by expression 'x'. _______________________________________________________________ exp (x) Returns the exponential function of any expression 'x'. _______________________________________________________________ floor (x) Returns the largest integral value that is less than or equal to the value produced by expression 'x'. _______________________________________________________________ frac (x) Returns the fraction part of 'x'. _______________________________________________________________ int (x) Returns the integer part of 'x'. _______________________________________________________________ ln (x) Returns the natural logarithm of any expression 'x'. An error is returned for values of 'x' that are less than or equal to 0. _______________________________________________________________ log10 (x) Returns the logarithm to the base 10 of any expression 'x'. An error is returned for values of 'x' that are less than or equal to 0. _______________________________________________________________ rnd (x,n) Rounds 'x' to 'n' decimal places, where 'n' can range between 15 and -15. If 'n' is negative, it rounds to the n-th power of 10 (e.g. round(1891,-2) = 1900). _______________________________________________________________ sqrt (x) = square_root Returns the square root of any expression 'x'. An error is returned for values of 'x' that are less than 0. Section 8.2.0 TRIG FUNCTIONS ________________________________________________________________ cos (angle) = cos OPTIONAL FORM: cos (angle,mode) ________________________________________________________________ cotan (angle) = cotan OPTIONAL FORM: cotan (angle,mode) ________________________________________________________________ sin (angle) = sin OPTIONAL FORM: sin (angle,mode) ________________________________________________________________ tan (angle) = tan OPTIONAL FORM: tan (angle,mode) Returns the sin, cos, tan, or cotan of any 'angle' in radians. Angles in degrees and grads can also be used by including the optional 'mode' parameter, where 0=RADIANS (default), 1=DEGREES, and 2=GRADS. ________________________________________________________________ acos (cos) = angle OPTIONAL FORM: acos (cos,mode) ________________________________________________________________ asin (sin) = angle OPTIONAL FORM: asin (sin,mode) ________________________________________________________________ atan (tan) = angle OPTIONAL FORM: atan (tan,mode) ________________________________________________________________ atan2 (x,y) = angle OPTIONAL FORM: atan2 (x,y,mode) Returns the arc sin, arc cos or arc tan in radians. Angles can also be returned in degrees and grads by including the optional 'mode' parameter, where 0=RADIANS (default), 1=DEGREES, and 2=GRADS. Section 8.3.0 FINANCIAL FUNCTIONS _______________________________________________________________ cterm (interest,fv,pv) = term Calculates the number of periods (term) required for a lump sum (pv) to reach a future amount (fv) at a given periodic interest rate. Example: +cterm(0.09/12,6543.28,5000.00) = 36 _______________________________________________________________ fv (payment,interest,term) = future_value Calculates the future value (amount accumulated), given the size of the payment, the interest rate per period, and the term (i.e. the total number of periods). Example: +fv(159.00,0.09/12,12*3) = $6543.28 _______________________________________________________________ pmt (principle,interest,term) = payment Calculates the size a payment required to pay off the principle on a loan, given the interest rate per period and the term (i.e. the total number of periods). Example: +pmt(5000.00,0.09/12,12*3) = $159.00 _______________________________________________________________ pv (payment,interest,term) = present_value Calculates the present value (principle of a loan), given the interest rate per period and the term (i.e. the total number of periods). Example: +pv(159.00,0.09/12,12*3) = $5000.04 _______________________________________________________________ rate (fv,pv,term) = interest_rate Calculates the interest rate, per period, required to increase an initial lump sum (pv) to a future value (fv) over a given number of periods (term). Example: +pmt(6543.28,5000.00,12*3) = 0.0075 _______________________________________________________________ term (payment,interest,fv) = term Calculates the number of periods (term) required to reach a future value (i.e. an accumulated amount). Example: +term(159.00,0.09/12,6543.28) = 36 ________________________________ Section 8.4.0 DATE FUNCTIONS Date and Time values are stored by the spreadsheet in the following format: yyyyddd.hhmmss Date _____| |_____ Time where: 'yyyy' equals the year (0000-9999) 'ddd' equals the day of the year (001-366) 'hh' equals hours (00-24) 'mm' equals minutes (00-59) 'ss' equal seconds (00-60) Example: 1991023.175912 --> January 23, 1991 17:59:12 NOTE: Legal time values (that can be used with the TIME functions) can range between 0.000000 and 9999365.235959; while legal date values (that can be used with both the TIME and DATE functions), can range between 1.000000 and 9999365.235959. That is, a date must contain a integer value of at least 1 (day 1). _______________________________________________________________ date (year,month,day) = yyyyddd.hhmmss Returns the 'datevalue' (yyyyddd.hhmmss), given the year, month, and day. _______________________________________________________________ year (yyyyddd.hhmmss) = yyyy Returns the year (0-9999), given a 'datevalue' (yyyyddd.hhmmss). _______________________________________________________________ month (yyyyddd.hhmmss) = month Returns the month (1-12), given a 'datevalue' (yyyyddd.hhmmss). _______________________________________________________________ day (yyyyddd.hhmmss) = day Returns the day of the month (1-31), given a 'datevalue' (yyyyddd.hhmmss). _______________________________________________________________ wkday (yyyyddd.hhmmss) = day-of-week Returns the day of the week (1=Sunday, 7=Saturday), given a 'datevalue' (yyyyddd.hhmmss). _______________________________________________________________ dpm (year,month) = number_of_days_in_month Returns the number of days in the month (28-31), given the year and month. _______________________________________________________________ dpy (year) = number_of_days_in_year Returns the number of days in the year (365-366), given the year. _______________________________________________________________ nxdate (yyyyddd.hhmmss,days) = yyyyddd.hhmmss Returns the adjusted 'datevalue', given a 'datevalue' (yyyyddd.hhmmss) and a positive or negative number of days. ________________________________ Section 8.5.0 TIME FUNCTIONS _______________________________________________________________ now () = yyyyddd.hhmmss Returns the current 'datevalue' in the following form: yyyyddd.hhmmss (where: 'yyyy' = year, 'ddd' = the day of the year, 'hh' = the hour, 'mm' = the minute, and 'ss' = the second (e.g. 1991023.175912 --> January 23, 1991 17:59:12). _______________________________________________________________ time (hh,mm,ss) = 0.hhmmss Returns the 'datevalue' (0.hhmmss), given the hour, minute, and second. _______________________________________________________________ hour (yyyyddd.hhmmss) = hh Returns the hour (0-23), given a 'datevalue' (yyyyddd.hhmmss). _______________________________________________________________ minute (ddddyyy.hhmmss) = mm Returns the minute (0-59), given a 'datevalue' (yyyyddd.hhmmss). _______________________________________________________________ second (yyyyddd.hhmmss) = ss Returns the seconds (0-59), given a 'datevalue' (yyyyddd.hhmmss). _______________________________________________________________ nxtime (yyyyddd.hhmmss,seconds) = yyyyddd.hhmmss Returns the adjusted 'datevalue' (hhmmss), given a 'datevalue' (yyyydd.hhmmss) and a positive or negative number of seconds. _______________________________________________________________ tmdif (yyyyddd.hhmmss,YYYYDDD.HHMMSS) = seconds Returns the positive or negative number of SECONDS between two 'datevalues' (yyyyddd.hhmmss). ________________________________ Section 8.6.0 STRING FUNCTIONS _______________________________________________________________ atof (label) = value Converts a character string to it numeric equivalent. Examples: atof ("123.55") = 123.55 _______________________________________________________________ strcmp (label1,label2) = status Compares 'label1' and 'label2', returning: (1) if 'label1' is GREATER than 'label2' (0) if 'label1' is EQUAL to 'label2' (-1) if 'label1' is LESS the 'label2' Examples: strcmp ([1,1],"Hello World!") strcmp ([1,1],[1,3]) _______________________________________________________________ strncmp (label1,label2,n) = status Compares the first 'n' characters of 'label1' and 'label2', returning: (1) if 'label1' is GREATER than 'label2' (0) if 'label1' is EQUAL to 'label2' (-1) if 'label1' is LESS the 'label2' Examples: strncmp ([1,1],"Hello World!",5) strncmp ([1,1],[1,3],5) _______________________________________________________________ strlen (label) = number_of_characters Returns the number of characters in a label (string). Examples: strlen ("Hello World!") strlen ([1,1]) _______________________________________________________________ index (label,c) = pos OPTIONAL FORM: index(label,c,start) Returns the 'position' of the first occurrence of a character 'c' in a 'label'. The search can be optionally started from any location by including that location in the optional 3rd parameter. Examples: index ("Hello World!",'o') = 5 index ([1,1],'o',6) = 8 _______________________________________________________________ strcat (cell,label) = errorcode Appends the contents of a 'label' or a 'string' to another 'cell'. Returns '0' if successful. Note, this function WILL NOT cause other cells to be recalculated as a result of any change to the cell in the first parameter. Examples: strcat([1,1],"Hello World!") strcat([1,1],[1,3]) _______________________________________________________________ strncat (cell,string,n) = errorcode Appends the first 'n' characters from a 'string' to the label of to another 'cell'. Returns '0' if successful. Note, this function WILL NOT cause other cells to be recalculated as a result of any change to the cell in the first parameter. Examples: strncat([1,1],"Hello World!",5) strcat([1,1],[1,3],5) _______________________________________________________________ strcpy (cell,string) = errorcode Copies the contents of a cell or of a 'string' to another 'cell'. Returns '0' if successful. Note, this function WILL NOT cause other cells to be recalculated as a result of any change to the cell in the first parameter. Examples: strcpy ([1,1],"Hello World!") strcpy ([1,1],[1,3]) _______________________________________________________________ strncpy (cell,string,n) = errorcode Copies the first 'n' characters of a 'string' to another 'cell'. Returns '0' if successful. Note, this this function WILL NOT cause other cells to be recalculated as a result of any change to the cell in the first parameter. Examples: strncpy ([1,1],"Hello World!",5) strncpy ([1,1],[1,3],5) Section 8.7.0 MISC FUNCTIONS ________________________________________________________________ col (offset) = current_column + offset ________________________________________________________________ lvl (offset) = current_worksheet_level + offset ________________________________________________________________ row (offset) = current_row + offset Adds the 'offset' to the row, column, or worksheet level of the cell that contains the formula and returns the value.